<?php
/**
 * dbfront
 * (c) 2010-2013 Stephen Adkins <spadkins@gmail.com>
 * This software began development in 2010.
 * It is based on code dating from 1999.
 *
 * License: GPL 2.0
 * The contents of this file may be used under the terms of
 * the GNU General Public License Version 2 or later (the "GPL").
 *
 * Software distributed under the License is distributed on an "AS IS" basis,
 * WITHOUT WARRANTY OF ANY KIND, either express or implied. See the License
 * for the specific language governing rights and limitations under the
 * License.
 * */
namespace API;
class DatabaseObject extends \App\SessionObject {
    /**
     * db_relationship(http_method, request, response, key) - select/insert/update/delete relationships between tables in the application metadata
     * http_method=get:    (without key) select and return a set according to parameters (with key) select        a single record
     * http_method=post:   (without key) insert/update     a set according to parameters (with key) insert/update a single record
     * http_method=put:    (without key) update            a set according to parameters (with key) update        a single record
     * http_method=delete: (without key) delete            a set according to parameters (with key) delete        a single record
     * When POST/PUT, use at least the following attributes to describe the relationship:
     *    "table_name": "tracked_url_log",
     *    "relationship_alias": "au",
     *    "relationship_label": "Auth User (User Id)",
     *    "to_table": "auth_user",
     *    "dependencies": "",
     *    "join_on": [{
     *        "from_db_name":     "app",
     *        "from_table_name":  "tracked_url_log",
     *        "from_column_name": "user_id",
     *        "to_db_name":       "auth",
     *        "to_table_name":    "auth_user",
     *        "to_column_name":   "user_id"
     *    }]
     * This will be constructed into the "join_on_clause" and stored in the db_relationship table.
     */
    public function db_relationship ($request, &$response, $key = null) {
        global $trace, $context;
        if ($trace) trace_entry();

        $http_method = isset($request['_http_method']) ? $request['_http_method'] : 'get';
        unset($request['_http_method']);
        $db = $context->metadb();
        $table_name = 'db_relationship';
        $object_set = new \API\ObjectSet();
        if ($http_method === 'get') {
            if (!isset($key)) {
                if (!isset($request['columns']))
                    $request['columns'] = 'table_name,relationship_alias,relationship_label,order_idx,to_table,join_on_clause,dependencies,to_zero_ind,to_many_ind';
                $object_set->execute_object_db_table($http_method, $request, $response, $db, $table_name);
            }
            elseif ($key === '_meta') {
                $object_set->execute_object_db_table_meta($http_method, $request, $response, $db, $table_name);
            }
            else {
                if (!isset($request['columns']))
                    $request['columns'] = 'table_name,relationship_alias,relationship_label,order_idx,to_table,join_on_clause,dependencies,to_zero_ind,to_many_ind';
                $object_set->execute_object_db_table_key($http_method, $request, $response, $db, $table_name, $key);
            }
        }
        else {
            if (is_assoc($request)) {
                $this->_supplement_db_relationship($request);
            }
            elseif (is_array($request)) {
                for ($i = 0; $i < count($request); $i++) {
                    $this->_supplement_db_relationship($request[$i]);
                }
            }
            if (!isset($key)) {
                $object_set->execute_object_db_table($http_method, $request, $response, $db, $table_name);
            }
            else {
                $object_set->execute_object_db_table_key($http_method, $request, $response, $db, $table_name, $key);
            }
        }
        if ($trace) trace_exit();
    }

    /**
     * _supplement_db_relationship(db_relationship) - create fields that are not supplied based on the ones that are supplied
     */
    private function _supplement_db_relationship (&$db_relationship) {
        global $trace, $context;
        if ($trace) trace_entry();
        if (isset($db_relationship['join_on_clause'])) unset($db_relationship['join_on_clause']);
        if (isset($db_relationship['join_on'])) {
            if (!isset($db_relationship['dependencies'])) $db_relationship['dependencies'] = '';
            if (!isset($db_relationship['table_name']) && !isset($db_relationship['to_table'])) {
                if (count($db_relationship['join_on']) != 1) throw new \Exception('table_name and to_table must be supplied directly for multi-column relationships');
                $db_relationship['table_name'] = $db_relationship['join_on'][0]['from_table_name'];
                $db_relationship['to_table']   = $db_relationship['join_on'][0]['to_table_name'];
            }
            if (!isset($db_relationship['relationship_alias']) && !isset($db_relationship['relationship_label'])) {
                $to_db_name = $db_relationship['join_on'][0]['to_db_name'];
                $to_db = $context->database($to_db_name);
                $table_def = $to_db->get_table_def($db_relationship['to_table']);
                $db_relationship['relationship_alias'] = $table_def['table_alias'];
                $table_label = $table_def['table_label'];
                $column_name = $db_relationship['join_on'][0]['from_column_name'];
                $column_label = $table_def['column'][$column_name]['column_label'];
                $db_relationship['relationship_label'] = "$table_label ($column_label)";
            }
            $db_relationship['join_on_clause'] = $this->make_join_on_clause($db_relationship['join_on']);
            unset($db_relationship['join_on']);
        }
        if ($trace) trace_exit();
    }

    /**
     * make_join_on_clause(join_on) - create a "join ... on ..." clause from the
     * The "join_on" argument is an array that looks like this.
     *    [{
     *        "from_db_name":     "app",
     *        "from_table_name":  "tracked_url_log",
     *        "from_column_name": "user_id",
     *        "to_db_name":       "auth",
     *        "to_table_name":    "auth_user",
     *        "to_column_name":   "user_id"
     *    }]
     * This will be constructed into the "join_on_clause" and stored in the db_relationship table.
     */
    private function make_join_on_clause ($join_on) {
        global $trace, $context;
        if ($trace) trace_entry();
        $join_on_clause = '';
        foreach ($join_on as $i => $join_col_def) {
            $from_db_name = $join_col_def['from_db_name'];
            $from_db = $context->database($from_db_name);
            $from_table_name = $join_col_def['from_table_name'];
            $from_table_def = $from_db->get_table_def($from_table_name);
            $from_column_name = $join_col_def['from_column_name'];
            $from_column_dbexpr = $from_table_def['column'][$from_column_name]['dbexpr'];
            if (!isset($from_column_dbexpr)) {
                $from_table_alias = $from_table_def['table_alias'];
                $from_column_dbexpr = "$from_table_alias.$from_column_name";
            }
            $to_db = $context->database($join_col_def['to_db_name']);
            $to_table_def = $to_db->get_table_def($join_col_def['to_table_name']);
            $to_column_name = $join_col_def['to_column_name'];
            $to_column_dbexpr = $to_table_def['column'][$to_column_name]['dbexpr'];
            if (!isset($to_column_dbexpr)) {
                $to_table_alias = $to_table_def['table_alias'];
                $to_column_dbexpr = "$to_table_alias.$from_column_name";
            }
            if ($i > 0) $join_on_clause .= ' and ';
            $join_on_clause .= "$to_column_dbexpr = $from_column_dbexpr";
        }
        if ($trace) trace_exit($join_on_clause);
        return($join_on_clause);
    }
}

